
*****************************************************************************************************************
*******************************************clean and prepare the data
foreach x in imp exp arr dis{
*********************************************
use "${hmrc_datadir}\Trade\\`x'", clear

*make the name of countries consistent
gen y=-year
sort codseq y
drop y
bysort codseq: gen n=_n==1
replace country=country[_n-1] if n==0
drop n

*gen fiscal year using perref
rename year calendar_year
sum perref
gen trade_year=int(perref/100)
gen trade_month=perref-trade_year*100
count if trade_year!=calendar_year
drop trade_year

gen year=calendar_year if trade_month>3
replace year=calendar_year-1 if trade_month<=3

keep if year<2012&year>1999


drop if svalue==0
drop if country=="Low Value Trade"
drop if traderid=="UNKNOWN"


drop if suite!=0
collapse (sum) svalue netmass suppunit, by (hs4 comcode traderid flow trade_month calendar year country)
save "${hmrc_datadir}\Trade\\`x'_collapsed_monthfy.dta", replace

collapse (sum) svalue netmass suppunit, by (hs4 comcode traderid flow calendar year country)

gen upweight=svalue/netmass
gen upquantity=svalue/suppunit
save "${hmrc_datadir}\Trade\\`x'_collapsed_fy.dta", replace

}

use "${hmrc_datadir}\Trade\arr_collapsed_fy.dta", clear
foreach i in dis imp exp{
append using "${hmrc_datadir}\Trade\\`i'_collapsed_fy.dta"
save "${hmrc_datadir}\Trade\total_collapsed_fy.dta", replace
}

use "${hmrc_datadir}\Trade\total_collapsed_fy.dta", clear
gen branch=substr( traderid, -13,.)
gen traderid_nobranch=subinstr( traderid , branch,"",.)
drop hs4
save "${hmrc_datadir}\Trade\total_collapsed_fy.dta", replace
